import java.io.*;
import java.sql.*;
import java.util.*;

import local.util.MyPrint;

public class DataBaseQuery {

    private String dbDriverName    = "sun.jdbc.odbc.JdbcOdbcDriver";
    private String dataBaseName    = "jdbc:odbc:stock_record";
    private String userName        = "wli";
    private String password        = "4test";
    private String tableName       = null;
    private String queryString     = null;
    private Vector tableHeadV = new Vector ();
    private Vector tableRecordV = new Vector ();
    private Hashtable sectorHashtable = new Hashtable ();
    private Hashtable industryHashtable = new Hashtable ();
    private Hashtable tickIndustryIdHashtable = new Hashtable ();
    private Hashtable tickTableHashtable = new Hashtable ();

    public static void main (String args[]) {

	String my_query_string = null;
	DataBaseQuery my_query = new DataBaseQuery ();
	Vector t_head_v   = new Vector();
	Vector t_record_v = new Vector();
	String tick = null;
	if (args.length >0) {
	    tick = args[0];
	}
       
	/*
	my_query.setQueryString ("SELECT Tick FROM Tick");
	my_query.lookup ( my_query_string );
	t_head_v   = my_query.getTableHeadVector();
	t_record_v = my_query.getTableRecordVector();
	MyPrint.printVectorVector(t_record_v,1);
	*/

	//my_query.setQueryString ("SELECT Tick FROM Tick");
	my_query.setTickTableHashtable("SELECT * FROM Tick", "Tick", "Tick");
	System.out.println(my_query.getTickTableHashtable());
    }

    public void setTickTableHashtable ( String query_string, String key, String value) {

	String driverName  = dbDriverName;
	String dbURL       = dataBaseName;
	String theUser     = userName;
	String thePwd      = password;
	String temp   = null;

	try {

	    Class.forName(driverName);
	    Connection con = DriverManager.getConnection(dbURL, theUser, thePwd);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query_string);

	    while (res.next()){
		if (key.equals(value)) {
		    temp = res.getString(key);
		    tickTableHashtable.put(temp , temp);
		}
		else {
		    tickTableHashtable.put(res.getString(key),res.getString(value));
		}
	    }

	    stmt.close();

	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }

    public void lookup ( String queryString) {

	String driverName  = dbDriverName;
	String dbURL       = dataBaseName;
	String theUser     = userName;
	String thePwd      = password;
	String qString     = queryString;
	Vector colum_heads = new Vector ();
	Vector rows        = new Vector ();
	int number_of_colums = 0;

	try {

	    Class.forName(driverName);
	    Connection con = DriverManager.getConnection(dbURL, theUser, thePwd);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(qString);

	    ResultSetMetaData rsmd = res.getMetaData();
	    number_of_colums = rsmd.getColumnCount();
	   
	    for (int i=1; i <= number_of_colums; ++i) {

		colum_heads.addElement (rsmd.getColumnName(i));
	    }

	    setTableHeadVector(colum_heads);
	  
	    while (res.next()){
		rows.addElement(getNextRow(res, rsmd));
	    }

	    setTableRecordVector(rows);

	    stmt.close();

	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }
  
    public void setSectorHashtable ( String query_string) {

	String driverName  = dbDriverName;
	String dbURL       = dataBaseName;
	String theUser     = userName;
	String thePwd      = password;

	try {

	    Class.forName(driverName);
	    Connection con = DriverManager.getConnection(dbURL, theUser, thePwd);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query_string);

	    while (res.next()){
		sectorHashtable.put(res.getString("Sector_Name"),res.getString("Sector_Id"));
	    }

	    stmt.close();

	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }

    public void setIndustryHashtable ( String query_string) {

	String driverName  = dbDriverName;
	String dbURL       = dataBaseName;
	String theUser     = userName;
	String thePwd      = password;

	try {

	    Class.forName(driverName);
	    Connection con = DriverManager.getConnection(dbURL, theUser, thePwd);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query_string);

	    while (res.next()){
		industryHashtable.put(res.getString("Industry_Name"),res.getString("Industry_Id"));
	    }

	    stmt.close();

	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }

    public void setTickIndustryIdHashtable ( String query_string) {

	String driverName  = dbDriverName;
	String dbURL       = dataBaseName;
	String theUser     = userName;
	String thePwd      = password;

	try {

	    Class.forName(driverName);
	    Connection con = DriverManager.getConnection(dbURL, theUser, thePwd);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query_string);

	    while (res.next()){
		tickIndustryIdHashtable.put(res.getString("Tick"),res.getString("Industry_Id"));
	    }

	    stmt.close();

	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
    }


 

    private Vector getNextRow( ResultSet rset,  ResultSetMetaData rsetmd )
	throws SQLException
    {
	Vector currentRow = new Vector();
      
	for ( int i = 1; i <= rsetmd.getColumnCount(); ++i ) {
	    switch( rsetmd.getColumnType( i ) ) {
            case Types.VARCHAR:
		currentRow.addElement( rset.getString( i ) );
		break;
	    case Types.INTEGER:
		currentRow.addElement( new Long( rset.getLong( i ) ) );
		break;
	    case Types.SMALLINT:
		currentRow.addElement( new Long( rset.getLong( i ) ) );
		break;
            default:
		currentRow.addElement( rset.getString( i ) );
		break;
		/*
		System.out.println( "Type was: " + 
				    rsetmd.getColumnTypeName( i ) );
		*/
	    }
	}
	//MyPrint.printVector(currentRow,4);
	return currentRow;
    }

    public void listAllDataType () {
	System.out.println("ARRAY:"+Types.ARRAY); 
	System.out.println("BIGINT:"+Types. BIGINT); 
	System.out.println("BINARY:"+Types. BINARY);
	System.out.println("BIT:"+Types. BIT);
	System.out.println("BLOB:"+Types. BLOB);
	System.out.println("BOOLEAN:"+Types. BOOLEAN); 
	System.out.println("CHAR:"+Types. CHAR); 
	System.out.println("CLOB:"+Types. CLOB);
	System.out.println("DATALINK:"+Types. DATALINK); 
	System.out.println("DECIMAL:"+Types. DECIMAL);
	System.out.println("DISTINCT:"+Types. DISTINCT); 
	System.out.println("DOUBLE:"+Types. DOUBLE);
	System.out.println("FLOAT:"+Types. FLOAT); 
	System.out.println("JAVA_OBJECT:"+Types. JAVA_OBJECT);
	System.out.println("LONGVARBINARY:"+Types. LONGVARBINARY);
	System.out.println("LONGVARCHAR:"+Types. LONGVARCHAR); 
	System.out.println("NULL:"+Types. NULL);
	System.out.println("NUMERIC:"+Types. NUMERIC);
	System.out.println("OTHER:"+Types. OTHER);
	System.out.println("REAL:"+Types. REAL);
	System.out.println("REF:"+Types. REF);
	System.out.println("SMALLINT:"+Types. SMALLINT);
	System.out.println("STRUCT:"+Types. STRUCT);
	System.out.println("TINYINT:"+Types. TINYINT);
	System.out.println("VARBINARY:"+Types. VARBINARY);
	System.out.println("INTEGER:"+Types.INTEGER); 
	System.out.println("VARCHAR:"+ Types.VARCHAR);
	System.out.println("DATE:"+Types.DATE);
	System.out.println("TIME:"+Types.TIME);
    }

    public Hashtable getSectorHashtable () {
	return sectorHashtable;
    }

    public Hashtable getIndustryHashtable () {
	return industryHashtable;
    }

    public Hashtable getTickIndustryIdHashtable () {
	return tickIndustryIdHashtable;
    }

    public Hashtable getTickTableHashtable () {
	return tickTableHashtable;
    }

    public void setQueryString (String query_string) {
	queryString = query_string;
    }

    public String getQueryString () {
	return queryString;
    }

    public void setTableHeadVector(Vector table_head_v) {
	tableHeadV = table_head_v;
    }

    public Vector getTableHeadVector() {
	return tableHeadV;
    }

    public void setTableRecordVector(Vector table_record_v) {
	tableRecordV = table_record_v;
    }

    public Vector getTableRecordVector() {
	return tableRecordV;
    }

}









